Database Testing

Database Testing is a software testing process used to verify the accuracy, integrity, consistency, reliability, security, and proper storage of data in an application's database. It ensures that data entered through an application is correctly stored, updated, retrieved, and deleted from the database.

In Selenium automation, database testing is generally combined with Selenium WebDriver, TestNG, and JDBC (Java Database Connectivity). Selenium handles browser and UI interactions, while JDBC allows Java automation code to connect to the database and execute SQL queries for backend validation. Selenium itself is primarily a browser automation tool and does not directly provide database connectivity. :contentReference[oaicite:0]{index=0}

Database validation is particularly useful in applications such as e-commerce, banking, healthcare, CRM, ERP, payment, registration, and enterprise systems where UI actions must correctly persist information in the backend database.

Course Resource: Selenium Training | Register for Course Demo

1. What is Database Testing?

Database Testing involves validating the data layer of an application. It checks whether the database correctly stores, processes, retrieves, and maintains application data according to business requirements.

For example, when a user registers on a website, the UI may display a successful registration message. Database testing can additionally verify whether the user's name, email, mobile number, role, and other information were correctly inserted into the appropriate database table.

User

  |
  v
Web Application
  |
  v
Application / API Layer
  |
  v
Database
  |
  v
Stored Data

2. Why is Database Testing Important?

Modern applications depend heavily on databases. A UI may appear to work correctly while incorrect, incomplete, duplicated, or corrupted data is being stored in the backend.

• Validates data accuracy.
• Verifies data integrity.
• Checks database consistency.
• Detects incorrect data insertion.
• Validates update and delete operations.
• Checks relationships between tables.
• Helps identify duplicate records.
• Validates application-to-database communication.
• Supports end-to-end testing.
• Helps verify business rules implemented at the data layer.

3. Database Testing with Selenium

Selenium and database testing serve different purposes. Selenium WebDriver automates the browser, while a database access technology such as JDBC is used to communicate with the database. TestNG can coordinate the test execution and assertions. :contentReference[oaicite:1]{index=1}

4. Database Testing Architecture

                 Selenium Test

                       |
                       v
                Web Application
                       |
             +---------+---------+
             |                   |
             v                   v
          UI Layer         Application Layer
                                 |
                                 v
                              Database
                                 ^
                                 |
                              JDBC
                                 |
                                 v
                           Test Validation
                                 |
                                 v
                              TestNG
                                 |
                                 v
                              Report

This architecture allows an automated test to perform an action through the UI and then independently verify the resulting database state.

5. What is JDBC?

JDBC stands for Java Database Connectivity. It is the standard Java API used to connect Java applications to relational databases and execute SQL statements.

JDBC provides interfaces and classes for establishing database connections, executing SQL queries, reading results, and managing database resources.

A typical JDBC workflow is:

Java Test

   |
   v
JDBC Driver
   |
   v
Database Connection
   |
   v
SQL Query
   |
   v
ResultSet
   |
   v
Validation

6. JDBC Database Testing Flow

1. Load or configure the appropriate JDBC driver.
2. Create a database connection.
3. Create a SQL statement or prepared statement.
4. Execute the SQL query.
5. Read the returned ResultSet.
6. Compare database values with expected values.
7. Close ResultSet, Statement, and Connection resources.

Using try-with-resources is a good Java practice because it helps ensure JDBC resources are closed automatically.

7. Common Database Testing Types

8. CRUD Operations

CRUD represents the four basic data operations:

• Create: Insert new data.
• Read: Retrieve existing data.
• Update: Modify existing data.
• Delete: Remove data.

CREATE  -> INSERT

READ    -> SELECT
UPDATE  -> UPDATE
DELETE  -> DELETE

9. Database Testing with SQL

SQL is commonly used to retrieve and validate database information.

SELECT * FROM users;

Specific records can be retrieved using conditions.

SELECT * FROM users

WHERE email = '[email protected]';

In automation code, dynamic values should generally be supplied through prepared statements rather than concatenating untrusted input into SQL.

10. Common SQL Commands Used in Testing

11. JDBC Connection Example

A basic JDBC connection can be established using DriverManager. For production frameworks, connection management may instead use a configured DataSource or connection pool.

import java.sql.Connection;

import java.sql.DriverManager;
 
public class DatabaseConnection {
 
    public static Connection getConnection() throws Exception {
 
        String url = "jdbc:mysql://localhost:3306/testdb";
        String username = "testuser";
        String password = "testpassword";
 
        return DriverManager.getConnection(
            url,
            username,
            password
        );
    }
}

Database URLs, usernames, and passwords should normally come from secure runtime configuration rather than being committed as plain text in source code.

12. JDBC SELECT Query Example

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
 
public class DatabaseReader {
 
    public void readUser() throws Exception {
 
        String sql =
            "SELECT username, email FROM users WHERE id = ?";
 
        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement statement =
                 connection.prepareStatement(sql)) {
 
            statement.setInt(1, 101);
 
            try (ResultSet resultSet = statement.executeQuery()) {
 
                while (resultSet.next()) {
 
                    String username =
                        resultSet.getString("username");
 
                    String email =
                        resultSet.getString("email");
 
                    System.out.println(username);
                    System.out.println(email);
                }
            }
        }
    }
}

13. Understanding ResultSet

ResultSet represents the data returned by a SQL query. The cursor initially points before the first row, and next() moves it to the next available row.

while (resultSet.next()) {

    String name = resultSet.getString("name");
    System.out.println(name);
}

Values can be retrieved by column name or column index.

String name = resultSet.getString("name");

int age = resultSet.getInt("age");

14. ResultSet Data Types

15. Database Validation with TestNG

TestNG can be used to execute database tests and verify results using assertions. TestNG is commonly used alongside Selenium in Java automation frameworks. :contentReference[oaicite:2]{index=2}

import org.testng.Assert;

import org.testng.annotations.Test;
 
@Test
public void verifyUser() throws Exception {
 
    String expectedUsername = "john";
 
    String actualUsername = getUsernameFromDatabase();
 
    Assert.assertEquals(
        actualUsername,
        expectedUsername
    );
}

16. Selenium UI and Database Validation

A common end-to-end scenario is to perform an operation through the UI and then verify the corresponding database record.

Test Starts

    |
    v
Open Application
    |
    v
Enter User Information
    |
    v
Click Submit
    |
    v
Application Saves Data
    |
    v
Connect to Database
    |
    v
Execute SELECT Query
    |
    v
Retrieve Record
    |
    v
Compare Expected vs Actual
    |
    v
TestNG Assertion
    |
    v
PASS / FAIL

17. Registration Database Validation

Suppose a user registration form contains name, email, and mobile number. Selenium can submit the form, and JDBC can verify that the expected record exists in the database.

String name = "John";

String email = "[email protected]";
String mobile = "9876543210";
 
driver.findElement(By.id("name")).sendKeys(name);
driver.findElement(By.id("email")).sendKeys(email);
driver.findElement(By.id("mobile")).sendKeys(mobile);
driver.findElement(By.id("register")).click();

After registration, the database can be queried using a unique identifier such as the email address.

String sql =

    "SELECT name, email, mobile FROM users WHERE email = ?";

18. Complete Selenium + JDBC Validation Example

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
 
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
 
import org.testng.Assert;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.BeforeMethod;
import org.testng.annotations.Test;
 
public class RegistrationDatabaseTest {
 
    private WebDriver driver;
 
    @BeforeMethod
    public void setup() {
        driver = new ChromeDriver();
        driver.manage().window().maximize();
        driver.get("https://example.com/register");
    }
 
    @Test
    public void verifyRegistrationInDatabase() throws Exception {
 
        String name = "John";
        String email = "[email protected]";
        String mobile = "9876543210";
 
        driver.findElement(By.id("name"))
                .sendKeys(name);
 
        driver.findElement(By.id("email"))
                .sendKeys(email);
 
        driver.findElement(By.id("mobile"))
                .sendKeys(mobile);
 
        driver.findElement(By.id("register"))
                .click();
 
        String sql =
            "SELECT name, email, mobile " +
            "FROM users WHERE email = ?";
 
        try (Connection connection =
                 DatabaseConnection.getConnection();
             PreparedStatement statement =
                 connection.prepareStatement(sql)) {
 
            statement.setString(1, email);
 
            try (ResultSet resultSet =
                     statement.executeQuery()) {
 
                Assert.assertTrue(
                    resultSet.next(),
                    "User record was not found"
                );
 
                Assert.assertEquals(
                    resultSet.getString("name"),
                    name
                );
 
                Assert.assertEquals(
                    resultSet.getString("email"),
                    email
                );
 
                Assert.assertEquals(
                    resultSet.getString("mobile"),
                    mobile
                );
            }
        }
    }
 
    @AfterMethod
    public void tearDown() {
        if (driver != null) {
            driver.quit();
        }
    }
}

This example demonstrates the important distinction between UI validation and persistence validation: Selenium verifies the browser workflow, while JDBC verifies what was stored in the database.

19. PreparedStatement

PreparedStatement is commonly preferred for parameterized SQL because values are supplied separately from the SQL statement.

String sql =

    "SELECT * FROM users WHERE email = ?";
 
PreparedStatement statement =
    connection.prepareStatement(sql);
 
statement.setString(1, email);
 
ResultSet resultSet =
    statement.executeQuery();

Parameterized SQL is preferable to constructing queries by concatenating external values.

20. INSERT Testing

INSERT testing verifies that a new record is correctly added to a database.

String sql =

    "INSERT INTO users(name, email) VALUES (?, ?)";
 
try (PreparedStatement statement =
         connection.prepareStatement(sql)) {
 
    statement.setString(1, "John");
    statement.setString(2, "[email protected]");
 
    int rows =
        statement.executeUpdate();
 
    System.out.println(
        "Rows inserted: " + rows
    );
}

21. UPDATE Testing

UPDATE testing verifies that existing records are modified correctly.

String sql =

    "UPDATE users SET status = ? WHERE id = ?";
 
try (PreparedStatement statement =
         connection.prepareStatement(sql)) {
 
    statement.setString(1, "ACTIVE");
    statement.setInt(2, 101);
 
    int rows =
        statement.executeUpdate();
 
    System.out.println(
        "Rows updated: " + rows
    );
}

22. DELETE Testing

DELETE testing verifies that a record is removed correctly when the application performs a delete operation.

String sql =

    "DELETE FROM users WHERE id = ?";
 
try (PreparedStatement statement =
         connection.prepareStatement(sql)) {
 
    statement.setInt(1, 101);
 
    int rows =
        statement.executeUpdate();
 
    System.out.println(
        "Rows deleted: " + rows
    );
}

23. CRUD Testing Flow

Create

  |
  v
INSERT
  |
  v
SELECT
  |
  v
Validate Record
  |
  v
UPDATE
  |
  v
SELECT
  |
  v
Validate Updated Record
  |
  v
DELETE
  |
  v
SELECT
  |
  v
Verify Record Removed

24. Count Validation

Database tests often need to verify the number of records affected by an operation.

String sql =

    "SELECT COUNT(*) FROM users";
 
try (PreparedStatement statement =
         connection.prepareStatement(sql);
     ResultSet resultSet =
         statement.executeQuery()) {
 
    resultSet.next();
 
    int count =
        resultSet.getInt(1);
 
    System.out.println(
        "Total users: " + count
    );
}

25. Verifying Record Existence

A simple database validation can determine whether a record exists.

String sql =

    "SELECT 1 FROM users WHERE email = ?";
 
try (PreparedStatement statement =
         connection.prepareStatement(sql)) {
 
    statement.setString(1, email);
 
    try (ResultSet resultSet =
             statement.executeQuery()) {
 
        Assert.assertTrue(
            resultSet.next(),
            "Expected record does not exist"
        );
    }
}

26. Database Testing with Multiple Tables

Real-world applications commonly store related information across multiple tables. Database tests may therefore need to validate relationships using SQL JOIN operations.

SELECT u.name, o.order_id

FROM users u
JOIN orders o
ON u.id = o.user_id
WHERE u.email = ?;

This can be useful for validating relationships between users and their orders, customers and transactions, or employees and departments.

27. Database Testing with JOIN

JOIN queries combine records from related tables.

28. Database Schema Testing

Schema Testing verifies the structure of the database rather than only checking individual records.

• Table names.
• Column names.
• Data types.
• Primary keys.
• Foreign keys.
• Unique constraints.
• Nullable and non-nullable columns.
• Indexes.
• Default values.

29. Primary Key Validation

A primary key uniquely identifies a record. Database testing can verify that records have unique identifiers and that required primary-key behavior is maintained.

SELECT id, COUNT(*)

FROM users
GROUP BY id
HAVING COUNT(*) > 1;

The expected result for a properly enforced primary key should not contain duplicate primary-key values.

30. Foreign Key Validation

Foreign keys establish relationships between tables. Testing can verify that child records reference valid parent records.

SELECT o.order_id

FROM orders o
LEFT JOIN users u
ON o.user_id = u.id
WHERE u.id IS NULL;

Unexpected rows can indicate orphaned records.

31. Null Value Testing

Database tests should verify whether columns correctly accept or reject NULL values according to application requirements.

SELECT *

FROM users
WHERE email IS NULL;

For a column that is required by the business rule, unexpected NULL records may indicate a defect.

32. Duplicate Data Testing

Duplicate data can cause functional problems in applications. Testing can identify duplicate values where uniqueness is expected.

SELECT email, COUNT(*)

FROM users
GROUP BY email
HAVING COUNT(*) > 1;

33. Data Integrity Testing

Data Integrity means that stored data remains accurate, complete, consistent, and valid throughout its lifecycle.

Examples include:

• Email addresses should be stored against the correct users.
• Orders should belong to valid customers.
• Order totals should correspond to the expected calculation.
• Required fields should not unexpectedly contain NULL values.
• Identifiers should remain unique.

34. Data Consistency Testing

Consistency testing verifies that the same business information remains logically consistent across related tables and application components.

Application Data

      |
      v
Users Table
      |
      v
Orders Table
      |
      v
Payments Table
      |
      v
Validate Relationships

35. Transaction Testing

Transaction testing verifies whether a group of database operations behaves correctly as a transaction.

Important transaction concepts include:

• Commit: Permanently saves transaction changes.
• Rollback: Reverts uncommitted changes.
• Atomicity: A transaction should behave as a complete unit.
• Consistency: Valid transactions should preserve database rules.

36. JDBC Transaction Example

connection.setAutoCommit(false);

 
try {
    // Database operation 1
    // Database operation 2
 
    connection.commit();
 
} catch (Exception e) {
 
    connection.rollback();
    throw e;
 
} finally {
 
    connection.setAutoCommit(true);
}

Transaction handling should be designed according to the application's actual database behavior and test requirements.

37. Database Testing for Login

Login testing can validate both the UI response and backend user information.

Login Page

    |
    v
Enter Username
    |
    v
Enter Password
    |
    v
Click Login
    |
    v
Application Authentication
    |
    +----------+
    |          |
    v          v
Success      Failure
    |
    v
Database Validation
    |
    v
User Status / Role / Account State

Database validation should be used to verify backend state where that state is relevant to the test requirement.

38. Database Testing for Registration

Registration is another common database validation scenario.

Passwords should not normally be compared against plaintext database values. Proper applications store passwords using secure password-hashing mechanisms.

39. Database Testing for E-Commerce

E-commerce applications contain many database-dependent workflows.

• User registration.
• Product creation.
• Product search.
• Shopping cart.
• Order creation.
• Order status updates.
• Inventory updates.
• Payment status.
• Shipping information.

Customer

   |
   v
Product
   |
   v
Cart
   |
   v
Order
   |
   v
Payment
   |
   v
Shipment
   |
   v
Database Validation

40. Database Testing for Order Creation

When a customer places an order, the test can verify that the order record and associated order items were persisted correctly.

SELECT order_id, user_id, total_amount, status

FROM orders
WHERE user_id = ?;

Additional queries can verify the corresponding records in an order-items table.

41. Database Testing for Inventory

An order may affect product inventory. A test can compare the expected inventory value with the value stored after the transaction.

SELECT quantity

FROM products
WHERE product_id = ?;

For example, if the application reduces inventory after a successful purchase, the test can verify that the database reflects the expected quantity.

42. Database Testing with Page Object Model

Database testing can be combined with the Page Object Model. Page classes handle browser interactions, while database utilities handle JDBC operations.

Test Class

    |
    +-- Page Object
    |      |
    |      +-- UI Actions
    |
    +-- Database Utility
           |
           +-- JDBC Connection
           +-- SQL Queries
           +-- Result Validation

This separation keeps UI interaction code and database access code easier to maintain.

43. Database Utility Class

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
 
public class DatabaseUtils {
 
    public static String getUserEmail(int userId)
            throws Exception {
 
        String sql =
            "SELECT email FROM users WHERE id = ?";
 
        try (Connection connection =
                 DatabaseConnection.getConnection();
             PreparedStatement statement =
                 connection.prepareStatement(sql)) {
 
            statement.setInt(1, userId);
 
            try (ResultSet resultSet =
                     statement.executeQuery()) {
 
                if (resultSet.next()) {
                    return resultSet.getString("email");
                }
            }
        }
 
        return null;
    }
}

44. Database Utility Advantages

• Centralizes JDBC logic.
• Reduces duplicate connection code.
• Makes tests easier to read.
• Improves maintainability.
• Encourages reusable database operations.
• Separates test logic from infrastructure code.

45. Database Configuration

Database configuration commonly contains:

• Database URL.
• Database name.
• Username.
• Password.
• Driver configuration.
• Connection-pool settings where applicable.

Environment-specific configuration should normally be supplied at runtime rather than hard-coded into test classes.

46. Database Testing with Properties File

db.url=jdbc:mysql://localhost:3306/testdb

db.username=testuser
db.password=secret

A Java configuration utility can load these values during test execution. Sensitive production credentials should instead be provided through an appropriate secret-management mechanism.

47. Database Testing with Maven

Maven can manage Selenium, TestNG, and JDBC driver dependencies in a Java automation project.

<dependencies>

    <dependency>
        <groupId>org.seleniumhq.selenium</groupId>
        <artifactId>selenium-java</artifactId>
    </dependency>
 
    <dependency>
        <groupId>org.testng</groupId>
        <artifactId>testng</artifactId>
        <scope>test</scope>
    </dependency>
 
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
    </dependency>
</dependencies>

The exact dependency versions should be selected according to the project's Java version, database version, and compatibility requirements.

48. Database Testing in CI/CD

Database tests can be integrated into CI/CD pipelines when the test environment provides controlled access to the required application and database.

Developer Commit

      |
      v
CI/CD Pipeline
      |
      v
Build
      |
      v
Application Deployment
      |
      v
Database Setup
      |
      v
Selenium + TestNG
      |
      v
JDBC Validation
      |
      v
Test Report

49. Database Testing with Test Data

Database-driven testing can retrieve test data directly from a database and supply it to automated tests. This approach can be combined with TestNG DataProviders for data-driven automation. JustAcademy's Selenium material also describes database-driven testing as a possible data source for a DataProvider. :contentReference[oaicite:3]{index=3}

Database

    |
    v
JDBC Utility
    |
    v
DataProvider
    |
    v
Test Method
    |
    v
Selenium WebDriver

50. Database Testing with DataProvider

@DataProvider(name = "users")

public Object[][] users() throws Exception {
 
    return DatabaseDataProvider.getUsers();
}
 
@Test(dataProvider = "users")
public void userTest(
        String username,
        String email) {
 
    System.out.println(username);
    System.out.println(email);
}

This approach is useful when test inputs are maintained in a database and need to be supplied dynamically to multiple test invocations.

51. Database Testing and Assertions

Assertions convert database validation into an explicit pass/fail condition.

Assert.assertEquals(

    actualEmail,
    expectedEmail,
    "Email does not match"
);
 
Assert.assertEquals(
    actualStatus,
    "ACTIVE",
    "Unexpected user status"
);

52. Database Testing and Expected Results

A robust test should clearly define what database state is expected after the application operation.

53. Database Cleanup

Automated tests should clean up data where appropriate so that one test does not interfere with another.

Test Setup

    |
    v
Create Test Data
    |
    v
Execute Test
    |
    v
Validate Result
    |
    v
Cleanup Test Data

Cleanup strategy depends on the environment and test purpose. Tests should avoid deleting shared or unrelated data.

54. Test Data Isolation

Test Data Isolation means each test should operate on data that it can safely control without causing unpredictable effects on other tests.

• Use unique test identifiers.
• Use dedicated test accounts where appropriate.
• Avoid modifying shared production-like records unnecessarily.
• Clean up records created by the test.
• Use transactions or reset mechanisms when appropriate.

55. Database Testing in Parallel Execution

Parallel execution can reduce test time, but database tests must be designed carefully. Concurrent tests may access or modify the same records.

Thread 1 --> Test User A --> Database

Thread 2 --> Test User B --> Database
Thread 3 --> Test User C --> Database

Using separate records and avoiding shared mutable state helps reduce interference between parallel tests.

56. Database Connection Management

Database connections are valuable resources and should be managed carefully.

• Open connections only when needed.
• Close ResultSet objects.
• Close PreparedStatement objects.
• Close Connection objects.
• Prefer try-with-resources.
• Use connection pools when appropriate for larger systems.
• Avoid creating unnecessary connections for every small operation.

57. Exception Handling in Database Testing

Database operations can fail because of connection problems, SQL errors, timeouts, incorrect credentials, unavailable databases, or unexpected data.

try {

    // Database operation
} catch (SQLException e) {
    System.out.println(
        "Database error: " + e.getMessage()
    );
    throw e;
}

Do not silently ignore database exceptions because doing so can cause a test to appear successful when validation never actually occurred.

58. Common Database Testing Errors

• Incorrect JDBC URL.
• Incorrect database credentials.
• Missing JDBC driver dependency.
• Database server unavailable.
• Incorrect SQL syntax.
• Incorrect table or column name.
• Wrong data type conversion.
• ResultSet cursor not moved using next().
• Database resources not closed.
• Test data conflicts between parallel tests.
• Incorrect expected database values.

59. Troubleshooting JDBC Connection Problems

60. Database Testing vs UI Testing

61. UI Validation vs Database Validation

Consider a registration workflow.

UI Validation

    |
    +-- Registration page opens
    +-- Fields accept input
    +-- Submit button works
    +-- Success message appears
 
Database Validation
    |
    +-- User record exists
    +-- Email is correct
    +-- Mobile is correct
    +-- Status is correct
    +-- Required relationships exist

Both perspectives can be useful when the requirement includes persistence of data.

62. Database Testing with APIs and UI

Modern applications may expose both browser-based and API-based workflows. Database validation can be placed behind either flow.

UI Test --------\

                 \
API Test ---------> Application ---> Database
                 /
Service Test ----/                 |
                                    v
                              JDBC Validation

This creates opportunities for end-to-end validation while keeping the database assertion focused on persistence requirements.

63. Database Testing Best Practices

• Use parameterized SQL queries.
• Do not hard-code sensitive database credentials.
• Use a reusable database utility layer.
• Keep database logic separate from page-object logic.
• Use clear SQL queries.
• Validate both positive and negative scenarios.
• Use unique test data where appropriate.
• Clean up test-created data.
• Close all JDBC resources.
• Use assertions instead of only printing database values.
• Protect sensitive information in logs and reports.
• Design database tests carefully before enabling parallel execution.

64. Common Mistakes in Database Testing

• Using production credentials in test source code.
• Writing SQL by unsafe string concatenation.
• Not closing connections.
• Ignoring SQLException.
• Validating only the UI and assuming the database is correct.
• Using shared test records across parallel tests.
• Deleting data that belongs to other tests.
• Using hard-coded IDs that may not exist.
• Not cleaning up temporary records.
• Creating large database queries unnecessarily.
• Logging passwords or other secrets.

65. Practical Project Structure

src

|-- test
|   |-- java
|       |-- tests
|       |   |-- RegistrationDatabaseTest.java
|       |   |-- LoginDatabaseTest.java
|       |   |-- OrderDatabaseTest.java
|       |
|       |-- pages
|       |   |-- RegistrationPage.java
|       |   |-- LoginPage.java
|       |   |-- CheckoutPage.java
|       |
|       |-- database
|       |   |-- DatabaseConnection.java
|       |   |-- DatabaseUtils.java
|       |   |-- UserQueries.java
|       |   |-- OrderQueries.java
|       |
|       |-- utilities
|           |-- ConfigReader.java
|           |-- DriverFactory.java
|           |-- TestDataUtils.java
|-- pom.xml

66. Recommended Database Testing Architecture

                Test Class

                    |
          +---------+---------+
          |                   |
          v                   v
     Page Objects       Database Utils
          |                   |
          v                   v
      Selenium             JDBC
          |                   |
          v                   v
     Application         Database
          |                   |
          +---------+---------+
                    |
                    v
                Assertions
                    |
                    v
                 TestNG
                    |
                    v
                 Reports

67. Complete Practical Database Testing Example

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
 
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
 
import org.testng.Assert;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.BeforeMethod;
import org.testng.annotations.Test;
 
public class UserDatabaseTest {
 
    private WebDriver driver;
 
    @BeforeMethod
    public void setup() {
        driver = new ChromeDriver();
        driver.manage().window().maximize();
        driver.get("https://example.com/register");
    }
 
    @Test
    public void verifyUserCreation() throws Exception {
 
        String name = "Test User";
        String email =
            "test" + System.currentTimeMillis()
            + "@example.com";
 
        driver.findElement(By.id("name"))
                .sendKeys(name);
 
        driver.findElement(By.id("email"))
                .sendKeys(email);
 
        driver.findElement(By.id("register"))
                .click();
 
        String sql =
            "SELECT name, email " +
            "FROM users WHERE email = ?";
 
        try (Connection connection =
                 DatabaseConnection.getConnection();
             PreparedStatement statement =
                 connection.prepareStatement(sql)) {
 
            statement.setString(1, email);
 
            try (ResultSet resultSet =
                     statement.executeQuery()) {
 
                Assert.assertTrue(
                    resultSet.next(),
                    "User was not created in database"
                );
 
                Assert.assertEquals(
                    resultSet.getString("name"),
                    name
                );
 
                Assert.assertEquals(
                    resultSet.getString("email"),
                    email
                );
            }
        }
    }
 
    @AfterMethod
    public void tearDown() {
        if (driver != null) {
            driver.quit();
        }
    }
}

68. Real-World Database Testing Flow

Requirement

    |
    v
Identify Database Impact
    |
    v
Create Test Data
    |
    v
Execute UI/API Operation
    |
    v
Application Processes Request
    |
    v
Database Updated
    |
    v
Execute SQL Query
    |
    v
Retrieve Database Record
    |
    v
Compare Expected vs Actual
    |
    v
TestNG Assertion
    |
    +------ PASS
    |
    +------ FAIL
    |
    v
Generate Report
    |
    v
Cleanup Test Data

69. Advantages of Database Testing Automation

• Speed: Repeated validation can be automated.
• Accuracy: Automated comparisons reduce manual checking.
• Reusability: Database utilities can be reused across tests.
• Coverage: Many records and scenarios can be validated.
• Integration: UI, API, database, and test frameworks can work together.
• Regression Support: Database validations can be included in regression suites.
• Early Detection: Persistence defects can be detected automatically.

70. Limitations of Database Testing Automation

• Requires database access and appropriate permissions.
• Test environments need controlled and reliable data.
• Database structure changes can affect tests.
• Complex SQL may require additional database expertise.
• Parallel execution can introduce data conflicts.
• Direct database access can make tests more tightly coupled to implementation details.
• Database credentials and sensitive data require careful security management.

71. When Should Database Validation Be Used?

Database validation is particularly useful when the requirement explicitly depends on persisted state.

• After user registration.
• After profile updates.
• After order creation.
• After payment processing.
• After inventory changes.
• After status changes.
• After data migration.
• When validating complex backend business rules.

Database checks should complement application-level testing rather than replacing the user-facing workflow. A UI test can establish that the application performs the workflow, while a database assertion can verify the resulting persisted state. :contentReference[oaicite:4]{index=4}

72. Interview Questions on Database Testing

1. What is Database Testing?

Database Testing verifies the accuracy, integrity, consistency, and correctness of data stored and processed by an application.

2. Can Selenium directly test a database?

Selenium WebDriver is designed primarily for browser automation. Database validation is normally performed using a database connectivity mechanism such as JDBC in Java-based Selenium frameworks. :contentReference[oaicite:5]{index=5}

3. What is JDBC?

JDBC stands for Java Database Connectivity and provides Java APIs for communicating with relational databases.

4. What is the purpose of ResultSet?

ResultSet represents rows returned by a database query and provides methods for reading column values.

5. What is PreparedStatement?

PreparedStatement represents a parameterized SQL statement and allows values to be supplied separately from the SQL command.

6. Why should PreparedStatement be preferred?

It provides a structured way to bind parameters and avoids unsafe SQL construction through direct string concatenation.

7. What are CRUD operations?

CRUD stands for Create, Read, Update, and Delete.

8. How can Selenium and JDBC work together?

Selenium performs the browser workflow, while JDBC queries the database to validate the resulting backend state.

9. How do you validate a record exists?

Execute a SELECT query and use ResultSet.next() to determine whether a matching record was returned.

10. How do you validate database values?

Retrieve the required values and compare them with expected values using assertions such as TestNG Assert.

11. What is database schema testing?

Schema testing verifies database structure, including tables, columns, data types, keys, constraints, and related definitions.

12. What is data integrity?

Data integrity means that stored information remains accurate, complete, valid, and consistent.

13. Why is database cleanup important?

Cleanup prevents test-created records from affecting later tests and helps maintain an isolated test environment.

14. How should database credentials be stored?

Credentials should generally be supplied through secure configuration or secret-management mechanisms rather than committed as plaintext source code.

15. Can database testing be combined with TestNG?

Yes. TestNG can manage test execution and assertions while JDBC performs database operations.

16. Can database data be used with DataProvider?

Yes. A DataProvider can obtain data through JDBC and supply it to test methods.

17. What is transaction testing?

Transaction testing verifies that database operations correctly commit or roll back according to application requirements.

18. What is data isolation?

Data isolation ensures that one test does not unintentionally interfere with another test's database state.

19. What is database validation in an e-commerce test?

It may include verifying users, products, orders, order items, inventory, payments, and shipment-related records.

20. What is the main benefit of Selenium plus database testing?

It allows an automated test to validate both the user-facing workflow and the relevant persisted backend state.

73. Quick Reference Table

74. Learning Roadmap for Database Testing

1. Understand basic database concepts.
2. Learn SQL fundamentals.
3. Learn SELECT, INSERT, UPDATE, and DELETE.
4. Understand primary and foreign keys.
5. Learn SQL JOIN operations.
6. Understand JDBC architecture.
7. Create a JDBC database connection.
8. Execute SELECT queries from Java.
9. Read ResultSet values.
10. Use PreparedStatement.
11. Integrate JDBC with TestNG.
12. Combine Selenium UI testing with database validation.
13. Create reusable Database Utility classes.
14. Integrate database tests with Page Object Model.
15. Use DataProvider with database test data.
16. Handle test data cleanup and isolation.
17. Integrate database tests with Maven.
18. Execute database tests in CI/CD environments.
19. Learn transaction and advanced database validation.

75. Practical Exercises

1. Create a JDBC connection to a test database.
2. Execute a SELECT query and print the results.
3. Validate that a user record exists.
4. Validate a user's email address.
5. Insert a test user and verify the record.
6. Update a user's status and validate the update.
7. Delete a test record and verify its removal.
8. Use Selenium to submit a registration form and verify the database record.
9. Create a reusable DatabaseUtils class.
10. Validate an e-commerce order after checkout.
11. Use a DataProvider to supply database records to tests.
12. Execute database tests through Maven.
13. Integrate database tests with a Selenium TestNG framework.

76. Real-World E-Commerce Database Example

Customer Registration

       |
       v
Users Table
       |
       v
Product Selection
       |
       v
Cart Table
       |
       v
Checkout
       |
       v
Orders Table
       |
       +------> Order Items
       |
       +------> Payment
       |
       +------> Shipment
       |
       v
Database Validation
       |
       v
TestNG Assertions
       |
       v
Test Report

For example, after checkout the test may verify that the order exists, the expected customer is associated with the order, order items were created, the total amount is correct, and the status is appropriate for the current workflow.

77. Final Database Testing Checklist

• Database connection is working.
• Required JDBC driver is available.
• Database credentials are securely configured.
• SQL queries are correct.
• PreparedStatement is used for dynamic values.
• ResultSet is handled correctly.
• Expected database values are clearly defined.
• TestNG assertions are used.
• Test-created data is isolated.
• Database resources are closed.
• Selenium and database responsibilities remain separated.
• Parallel execution does not cause data conflicts.
• Sensitive information is not exposed in logs or reports.

78. Summary

Database Testing is an important part of complete software testing because an application's UI can appear correct while incorrect data is being stored or retrieved in the backend.

In Java-based Selenium automation, Selenium WebDriver can perform browser interactions, JDBC can connect Java code to the database, and TestNG can execute the tests and perform assertions. This combination can validate workflows from the user's browser action through to the persisted database state. :contentReference[oaicite:6]{index=6}

Important concepts include JDBC connections, SQL queries, PreparedStatement, ResultSet, CRUD operations, data integrity, schema validation, transactions, database cleanup, test-data isolation, DataProvider integration, Page Object Model, Maven, and CI/CD execution.

For scalable Selenium automation frameworks, database logic should generally be maintained in reusable utilities while page classes remain responsible for UI interactions. This separation makes the framework easier to maintain and extend.

79. Course Resources

Learn more about Selenium automation and related testing concepts:

• Selenium Training
• Register for Selenium Course Demo

Final Takeaway: Database Testing validates whether application data is stored, updated, retrieved, and maintained correctly. When combined with Selenium WebDriver, JDBC, and TestNG, it can provide end-to-end validation of both the user-facing workflow and the relevant backend database state.